package com.globalwave.common.db;

import org.hibernate.dialect.DB2Dialect;
import org.hibernate.dialect.MySQL5Dialect;
import org.hibernate.dialect.Oracle9Dialect;

public class SqlUtils {
    final public static int DB_TYPE_ORACLE = 0 ;
    final public static int DB_TYPE_DB2 = 2 ;
    final public static int DB_TYPE_MYSQL = 5 ;
    //final public static int DB_TYPE_MSSQL = 8 ;

    public static String getLimitString(final int type, final String sql) {   

    	if (type == DB_TYPE_ORACLE) {
	        
	        return new Oracle9Dialect().getLimitString(sql, true) ;
    	} else if (type == DB_TYPE_DB2){ 
	        
	        return new DB2Dialect().getLimitString(sql, true) ;    		
    	} else if (type == DB_TYPE_MYSQL){
	        
	        return new MySQL5Dialect().getLimitString(sql, true) ;    		
    	} else {
    		return sql ;
    	}
        //return pagingSelect.toString();   
    } 
    public static String getUnlockRead(final int type, final String sql) {   

    	if (type == DB_TYPE_ORACLE) {
	        
	        return sql ;
    	} else if (type == DB_TYPE_DB2){ 
	        
	        return sql + " with ur " ;    		
    	} else if (type == DB_TYPE_MYSQL){
	        
	        return sql ;    		
    	} else {
    		return sql ;
    	}
        //return pagingSelect.toString();   
    }
    
    public static StringBuffer getUnlockRead(final int type, final StringBuffer sql) {   

    	if (type == DB_TYPE_ORACLE) {
	        
	        return sql ;
    	} else if (type == DB_TYPE_DB2){ 
	        
	        return sql.append(" with ur ") ;    		
    	} else if (type == DB_TYPE_MYSQL){
	        
	        return sql ;    		
    	} else {
    		return sql ;
    	}
        //return pagingSelect.toString();   
    }
    public static int[] getLimitParams(final int type, int pageIndex, int pageSize) {   

    	if (type == DB_TYPE_ORACLE) {
    		return new int[]{(pageIndex * pageSize), ((pageIndex-1) * pageSize)} ;
    	} else if (type == DB_TYPE_DB2){ 
	        //return int[]{((pageIndex-1) * pageSize), pageSize} ;
    		return null ;
    	} else if (type == DB_TYPE_MYSQL){ 
	        return new int[]{((pageIndex-1) * pageSize), pageSize} ;  		
    	} else {
    		return null ;
    	}
        //return pagingSelect.toString();   
    } 
    
    public static String getFetchFirstXRowsSql(final int type, final String sql, final int size) {   

    	if (type == DB_TYPE_ORACLE) {
    		return "select * from (" + sql + ") a where rownum<=" + size;
    	} else if (type == DB_TYPE_DB2){ 
	        //return int[]{((pageIndex-1) * pageSize), pageSize} ;
    		return sql + " fetch first " + size + " rows only with ur";
    	} else if (type == DB_TYPE_MYSQL){ 
	        return sql ;  		
    	} else {
    		return sql ;
    	}
        //return pagingSelect.toString();   
    } 

    public static StringBuffer getFetchFirstXRowsSql(final int type, final StringBuffer sql, final int size) {   

    	StringBuffer result = new StringBuffer() ;
    	if (type == DB_TYPE_ORACLE) {
    		return result.append("select * from (").append(sql).append(") a where rownum<=").append(size);
    	} else if (type == DB_TYPE_DB2){ 
	        //return int[]{((pageIndex-1) * pageSize), pageSize} ;
    		return result.append(sql).append(" fetch first ").append(size).append(" rows only with ur");
    	} else if (type == DB_TYPE_MYSQL){ 
	        return sql ;  		
    	} else {
    		return sql ;
    	}
        //return pagingSelect.toString();   
    } 
    
    /**
     * 去除hql的select 子句，未考虑union的情况,用于pagedQuery.
     *
     * @see #pagedQuery(String,int,int,Object[])
     */
    public static String getCountSql(final String sql) {
    	
        String tempSql = sql.toLowerCase().trim() ;
        
        int beginPos = 7; // start after select
        int nextFormPos = tempSql.indexOf("from ", beginPos);
        int nextSelectPos = tempSql.indexOf("select ", beginPos);
        
        while (nextSelectPos >0 && nextFormPos > nextSelectPos) {
        	beginPos = nextFormPos + 4 ;

            nextFormPos = tempSql.indexOf("from ", beginPos);
            nextSelectPos = tempSql.indexOf("select ", beginPos);
        }
        
    	
        int endPos = tempSql.indexOf("order by");
        
        if (endPos > 0) {
            return "SELECT COUNT(*) " + sql.trim().substring(nextFormPos, endPos -1);
        } else {
            return "SELECT COUNT(*) " + sql.trim().substring(nextFormPos);
        }
    }
}
